'''
数据访问
'''
import sqlite3
from sqlite3.dbapi2 import Connection, Row, connect
from typing import List


def get_connection():
    connect = sqlite3.connect('words.db')
    return connect


# 插入一条数据
def insert_product(connection: Connection, word: str, word_explain: str) -> int:
    sql = "INSERT INTO words (word, word_explain) VALUES ('" + \
        word + "','" + word_explain + "')"
    connection.execute(sql)
    connection.commit()


# 默认匹配，即在所有位置查询
def list(connection: Connection, input: str) -> List:
    sql = "select * from words where word like '%" + \
        input+"%' or word_explain like '%"+input+"%'"
    cursor = connection.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows


# 全匹配
def list_by_word_all(connection: Connection, word: str) -> List:
    sql = "select * from words where word like '"+word+"'"
    cursor = connection.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows


# 匹配开头
def list_by_word_star(connection: Connection, word: str) -> List:
    sql = "select * from words where word like '"+word+"%'"
    cursor = connection.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows


# 匹配结尾
def list_by_word_end(connection: Connection, word: str) -> List:
    sql = "select * from words where word like '%"+word+"'"
    cursor = connection.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows


# 匹配解释
def list_by_explain(connection: Connection, word_explain: str) -> List:
    sql = "select * from words where word_explain like '%"+word_explain+"%'"
    cursor = connection.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows


# 主方法
if __name__ == '__main__':
    connection = get_connection()
    row = list_by_explain(connection, "学习")
    for i in row:
        print(i)
    connection.close()
